using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Data.SqlClient;

namespace LanguageTool
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        void DbCreate(string yol)
        {
            SQLiteConnection cn = new SQLiteConnection("Data Source=" + yol + ";Pooling=true;FailIfMissing=false;UseUTF16Encoding=true;Version=3");
            cn.Open();
            StringBuilder sb = new StringBuilder();
            sb.Append("CREATE TABLE [Language] (" + Environment.NewLine);
            sb.Append("[rec_id] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[language_id] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[resource_type] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[resource_module] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[resource_menu_no] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[resource_no] INTEGER  NULL," + Environment.NewLine);
            sb.Append("[resource_text] VARCHAR(500)  NULL," + Environment.NewLine);
            sb.Append("PRIMARY KEY ([language_id],[resource_type],[resource_module],[resource_menu_no],[resource_no])" + Environment.NewLine);
            sb.Append(")" + Environment.NewLine);

            SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        void DataTransfer(string yol)
        {
            SqlConnection power = new SqlConnection("Data Source=" + textBox1.Text + ";Database=POWER;user id=sa;");
            power.Open();
            SqlCommand cmd = new SqlCommand("select * from PW_LANGUAGE", power);
            SqlDataReader rd = cmd.ExecuteReader();
            int rec;
            int lng;
            int typ;
            int mod;
            int men;
            int no;
            string txt;

            #region SqlITE
            SQLiteConnection cn = new SQLiteConnection("Data Source=" + yol + ";Pooling=true;FailIfMissing=false");
            cn.Open();
            SQLiteTransaction trn;
            trn = cn.BeginTransaction();
            SQLiteCommand itecmd = new SQLiteCommand("INSERT INTO Language (rec_id,language_id,resource_type,resource_module,resource_menu_no,resource_no,resource_text) values (@rec,@lng,@typ,@mod,@men,@no,@txt)", cn);
            itecmd.Transaction = trn;
            itecmd.Parameters.Add("@rec", DbType.Int32);
            itecmd.Parameters.Add("@lng", DbType.Int32);
            itecmd.Parameters.Add("@typ", DbType.Int32);
            itecmd.Parameters.Add("@mod", DbType.Int32);
            itecmd.Parameters.Add("@men", DbType.Int32);
            itecmd.Parameters.Add("@no", DbType.Int32);
            itecmd.Parameters.Add("@txt", DbType.String);
            #endregion

            while (rd.Read())
            {
                rec = rd.GetInt32(0);
                lng = rd.GetInt32(1);
                typ = rd.GetInt32(2);
                mod = rd.GetInt32(3);
                men = rd.GetInt32(4);
                no = rd.GetInt32(5);
                txt = rd.GetString(6);
                itecmd.Parameters["@rec"].Value = rec;
                itecmd.Parameters["@lng"].Value = lng;
                itecmd.Parameters["@typ"].Value = typ;
                itecmd.Parameters["@mod"].Value = mod;
                itecmd.Parameters["@men"].Value = men;
                itecmd.Parameters["@no"].Value = no;
                itecmd.Parameters["@txt"].Value = txt;
                itecmd.ExecuteNonQuery();
            }
            trn.Commit();
            power.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                DbCreate(folderBrowserDialog1.SelectedPath + "\\Language.db");
                DataTransfer(folderBrowserDialog1.SelectedPath + "\\Language.db");
                MessageBox.Show("Bitti");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                SQLiteConnection cn = new SQLiteConnection("Data Source=" + dlg.FileName + ";Pooling=true;FailIfMissing=false");
                cn.Open();
                SQLiteDataAdapter adp = new SQLiteDataAdapter("select * from Language", cn);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                cn.Close();
                dataGridView1.DataSource = dt;
            }
        }
    }
}